
/*------------------------------------------------------------------------------

					TABLE 2: INTER-COHORT REDISTRIBUTION
						
------------------------------------------------------------------------------*/

use "$output/DA.dta", clear

*** Deal with acquisitions

* consolidate targets and acquirers before they merge

replace entite = "GENERALI_VIE" if inlist(entite,"GENERALI_VIE","GENERALI_ASSURANCES_VIE","GPA_VIE","GUARDIAN_VIE")
replace entite = "CNP_ASSURANCES" if inlist(entite,"CNP_ASSURANCES","ECUREUIL_VIE")
replace entite = "CARDIF_ASSURANCE_VIE" if inlist(entite,"CARDIF_ASSURANCE_VIE","NATIO_VIE")
replace entite = "NATIXIS_ASSURANCES_PARTENAIRES" if inlist(entite,"NATIXIS_ASSURANCES_PARTENAIRES","ASSURANCES_BANQUE_POPULAIRE_VIE")

collapse (sum) PM_ouv_ig PM_clo_ig Revalo_ig Inflow_ig Outflow_ig Reserve_ig Reserve_ig_lag (mean) euro2015, by(entite year)

* bring back matricule

merge m:m entite using "$output/DA", keepusing(matricule) keep(3) nogen

*** Keep the 50 insurers we observe throughout the sample period

* NB: they account for 94% of provisions

qui egen PMtot = sum(PM_clo_ig)
qui sum PMtot
local tot0 = r(mean)
drop PMtot

* drop CAPMA_&_CAPMI because missing assets in 2015
egen n = count(year), by(matricule)
gen suppr = n<17 | entite=="CAPMA_&_CAPMI"
drop if suppr==1
drop suppr

qui egen PMtot = sum(PM_clo_ig)
qui sum PMtot
local tot1 = r(mean)
drop PMtot
display `tot1'/`tot0'

distinct entite

xtset matricule year
sort matricule year



*** Assume constant hazard rate for outflows

* h`s' at year=t: hazard rate in year t for cohort s
* h`s' are defined up to a multiplicative factor, because we normalize at the end so that outflows by cohort add up to total outflows

forvalues i = 1998(1)2014 {

	qui gen h`i' = .

}

forvalues t = 1999(1)2015 {

	local tm = `t'-1
	
	forvalues i = 1998(1)`tm' {
		
		qui replace h`i' = 1 if year==`t'
	
	}

}


*** Rename variables

* provisions at year-end, year-start, and year-to-year discrepancy
gen P = PM_clo_ig
gen Ps = PM_ouv_ig
gen alpha = Ps/l.P

* flows (euro amount)
gen Pp = Inflow_ig
gen Pm = Outflow_ig

* average provision
gen Pa = Ps + Pp/2 - Pm/2

* credited rate (before fees)
gen y = Revalo_ig/Pa

* fee rate
gen f = (Ps+Pp-Pm+Revalo_ig-P)/Pa


*** Account value by entering cohorts

forvalues i = 1998(1)2015 {
	
	qui gen Ps`i' = .
	qui gen P`i' = .
	qui gen Pm`i' = .

}

forvalues t = 1999(1)2015 {

	local tm = `t'-1

	* year start: i<t

	forvalues i = 1998(1)`tm' {

		* t=1999 (i=1998)
		qui replace Ps`i' = Ps if year==`t' & `t'==1999

		* t>1999 (i=1998...t-1)
		qui replace Ps`i' = alpha*l.P`i' if year==`t' & `t'>1999
	
	}

	* redemptions: i<t
	
	qui gen total = 0 if year==`t'

	forvalues i = 1998(1)`tm' {
	
		qui replace Pm`i' = h`i'*Ps`i' if year==`t'
		qui replace total = total+h`i'*Ps`i' if year==`t'
	
	}

	forvalues i = 1998(1)`tm' {
		qui replace Pm`i' = Pm`i'/total*Pm if year==`t'
	}
	drop total

	* year end: i<=t

	forvalues i = 1998(1)`t' {

		qui replace P`i' = Ps`i'*(1+y-f) - Pm`i'*(1+(y-f)/2) if year==`t' & `i'<`t'
		qui replace P`i' = Pp*(1+(y-f)/2) if year==`t' & `i'==`t'
	
	}

}



*** Account value by entering/exiting cohorts

forvalues i = 1998(1)2015 {

	local ip = `i'+1
	
	forvalues j = `ip'(1)2016 {
		
		qui gen P`i'`j' = .
	
	}

}

* i<j

forvalues i = 1998(1)2015 {
	
	local ip = `i'+1
	
	forvalues j = `ip'(1)2016 {
		
		* t=j-1...i
		
		local jm = `j'-1
		
		forvalues t = `jm'(-1)`i' {
		
			* t=j-1
			qui replace P`i'`j' = P`i' if `j'==2016 & year==`j'-1 & year==`t'
			qui replace P`i'`j' = (1+(f.y-f.f)/2) / (f.alpha*(1+f.y-f.f)) * f.Pm`i' if `j'<2016 & year==`j'-1 & year==`t'
			
			* t<j-1
			qui replace P`i'`j' = f.P`i'`j' / (f.alpha*(1+f.y-f.f)) if year<`j'-1 & year==`t'
		
		}
	
	}

}



*** Inflow by exiting cohort

forvalues j = 2000(1)2016 {
	
	qui gen Pp`j' = .
	
	* t=1999...j-1
	local jm = `j'-1
	
	forvalues t = 1999(1)`jm' {
	
		qui replace Pp`j' = P`t'`j' / (1+(y-f)/2) if year==`t'
		
	}

}



*** Discount rate

gen R = Reserve_ig
gen A = P+R
gen Rs = Reserve_ig_lag
gen As = Ps+Rs

qui gen rho = 1 if year==1999
forvalues t = 2000(1)2015 {
	qui replace rho = l.rho/alpha if year==`t'
}



*** Annual total transfer amount

gen Tt = -(R-Rs) if year>1999



*** Define average invested amount on which interests are calculated

forvalues i = 1998(1)2015 {
	
	local ip = max(`i'+1,2000)
	
	forvalues j = `ip'(1)2016 {
		
		qui gen Pa`i'`j' = .
		
		*t=i
		qui replace Pa`i'`j' = Pp`j'/2 if year==`i'
		
		*i<t<j
		qui replace Pa`i'`j' = alpha*l.P`i'`j' if year>`i' & year<`j'

		*t=j
		qui replace Pa`i'`j' = alpha*l.P`i'`j'-Pm`i'/2 if year==`j'
	
	}

}



*** Annual transfer amount by cohort

forvalues i = 1998(1)2015 {

	local ip = max(`i'+1,2000)

	forvalues j = `ip'(1)2016 {

		qui gen Tt`i'`j' = Tt * Pa`i'`j' / Pa if year>=`i' & year<=`j'
	
	}

}



*** Lifetime transfer amount by cohort (defined only if full history)

forvalues i = 2000(1)2014 {

	local ip = `i'+1

	forvalues j = `ip'(1)2015 {

		qui egen T`i'`j' = sum(rho*Tt`i'`j'), by(matricule)

	}

}



*** Annualized lifetime transfer amount by cohort (defined only if full history)

* lifetime account value

forvalues i = 2000(1)2014 {

	local ip = `i'+1

	forvalues j = `ip'(1)2015 {

		qui egen totPa`i'`j' = sum(rho*Pa`i'`j'), by(matricule)
	
	}

}

* multiply by weight of each year

forvalues i = 2000(1)2014 {
	
	local ip = `i'+1

	forvalues j = `ip'(1)2015 {

		qui gen Ttn`i'`j' = T`i'`j' * Pa`i'`j' / totPa`i'`j' if year>=`i' & year<=`j'

	}

}



*** Average net transfer over cohorts with full history

capture drop Ttn_fullhis Pa_fullhis

* total transfer amount to cohorts with full history

gen Ttn_fullhis = 0

* total account value of cohorts with full history

gen Pa_fullhis = 0

* calculate total amounts

forvalues i = 2000(1)2014 {
	
	local ip = `i'+1

	forvalues j = `ip'(1)2015 {

		qui replace Ttn_fullhis = Ttn_fullhis + abs(Ttn`i'`j') if Ttn`i'`j'<.

		qui replace Pa_fullhis = Pa_fullhis + Pa`i'`j' if Pa`i'`j'<.

	}

}



*** TABLE 2 PANEL A: HEATMAP

preserve

drop if year==1999

collapse (sum) Tt Pa, by(year)

forvalues i = 2000(1)2015 {
	
	forvalues j = `i'(1)2015 {
	
		* (exit year,entry year)
		qui gen tau`j'`i' = Tt/Pa*100 if year>=`i' & year<=`j'
	
	}

}

collapse (mean) tau*

gen one = 1
qui reshape long tau2000 tau2001 tau2002 tau2003 tau2004 tau2005 tau2006 tau2007 tau2008 tau2009 tau2010 tau2011 tau2012 tau2013 tau2014 tau2015, i(one) j(entry)
drop one

sort entry

renpfix tau exit

export excel entry exit2000 exit2001 exit2002 exit2003 exit2004 exit2005 exit2006 exit2007 exit2008 exit2009 exit2010 exit2011 exit2012 exit2013 exit2014 exit2015 using "$results/heatmap", firstrow(variables) replace

restore



*** TABLE 2 PANEL B: Aggregate inter-cohort transfer

preserve

drop if year==1999

* year-level transfer

collapse (sum) Ttn_fullhis Pa_fullhis, by(year)

gen ict = Ttn_fullhis/Pa_fullhis
label var ict "Inter-cohort transfer (\% account value/year)"

eststo clear

estpost tabstat ict, stat(mean)

estout using "$results/inter-cohort-transfer.tex", replace style(tex) cells("mean(fmt(%12.3f) label(Mean))") label mlabels(none) collabels(none) varwidth(50)

restore
